<?php
class ProjectMaterialAnalyzeDataAction extends CAction {
	public function run($node, $projectId, $keyword){
		JHTML::jsonHeader();
		if(!$projectId){
			echo CJSON::encode(array());
			return;
		}
		if($node=='root'){
			$this->expandRoot($projectId, $keyword);
			return;
		}
		if(preg_match('/^c_([0-9]+)$/', $node,$match)){
			$this->expandCategory($match[1], $projectId, $keyword);
			return;
		}
		if(preg_match('/^m_([0-9]+)$/', $node, $match)){
			$this->expandMaterial($match[1],$projectId);
			return;
		}
	}
	
	protected function expandRoot($projectId, $keyword){
		$commandBuilder = Yii::app()->db->getSchema()->getCommandBuilder();
		$categoryModel=MaterialCategory::model();
		
		$criteria=new CDbCriteria();
		$criteria->distinct=true;
		$criteria->join = 'INNER JOIN {{material}} as material on material.categoryId='
			.$categoryModel->getTableAlias().'.id INNER JOIN {{material_model}} as materialModel on material.id=materialModel.materialId '.
			'LEFT OUTER JOIN {{material_receipt_item}} receiptItem ON receiptItem.materialModelId=materialModel.id'
				.' LEFT OUTER JOIN {{material_receipt}} receipt ON receiptItem.materialReceiptId=receipt.id';
		if($keyword){
			$criteria->addSearchCondition('material.name', $keyword);
		}
		$criteria->compare('receipt.projectId', $projectId);
		$criteria->addNotInCondition('receiptItem.id', array(null));
		$criteria->select = $categoryModel->getTableAlias().'.id, '.$categoryModel->getTableAlias().'.name,'.' sum(totalPrice) as grandCost';
		
		$criteria->group='categoryId';
		$command = $commandBuilder->createFindCommand($categoryModel->getTableSchema(), $criteria);
		$rows = $command->query();
		$data=array();
		if($rows->valid()){
			foreach($rows as $row) {
				$data[]=array(
					'id'=>'c_'.$row['id'],
					'name'=>$row['name'],
					'grandCost'=>$row['grandCost']
				);
			}
		}
		echo CJSON::encode($data);
	}
	
	protected function expandCategory($categoryId, $projectId, $keyword){
		$commandBuilder = Yii::app()->db->getSchema()->getCommandBuilder();
		$materialModel=Material::model();
		$alias=$materialModel->getTableAlias();
		
		$criteria=new CDbCriteria();
		$criteria->distinct=true;
		$criteria->join = 'INNER JOIN {{material_model}} as materialModel on '.$materialModel->getTableAlias().'.id=materialModel.materialId '.
			'INNER JOIN {{material_receipt_item}} receiptItem ON receiptItem.materialModelId=materialModel.id'
				.' LEFT OUTER JOIN {{material_receipt}} receipt ON receiptItem.materialReceiptId=receipt.id';
		if($keyword){
			$criteria->addSearchCondition($materialModel->getTableAlias().'.name', $keyword);
		}
		$criteria->compare('receipt.projectId', $projectId);
		$criteria->compare('categoryId', $categoryId);
		$criteria->group='materialId';
		$criteria->order=$alias.'.name ASC';
		$criteria->select = $alias.'.id, '.$alias.'.name,'
			.' sum(totalPrice) as grandCost';
		$command = $commandBuilder->createFindCommand($materialModel->getTableSchema(), $criteria);
		$rows = $command->query();
		$data=array();
		if($rows->valid()){
			foreach($rows as $row) {
				$data[]=array(
					'id'=>'m_'.$row['id'],
					'name'=>$row['name'],
					'grandCost'=>$row['grandCost']
				);
			}
		}
		echo CJSON::encode($data);
	}
	
	protected function expandMaterial($materialId, $projectId){
		$totalRequisitions=$this->lookUpRequisitions($materialId, $projectId);
		$totalWarehouse=$this->lookUpWarehouse($materialId, $projectId);
		
		$commandBuilder = Yii::app()->db->getSchema()->getCommandBuilder();
		$materialModelModel=MaterialModel::model();
		$alias=$materialModelModel->getTableAlias();
	
		$criteria=new CDbCriteria();
		$criteria->join ='INNER JOIN {{material_receipt_item}} receiptItem ON receiptItem.materialModelId='.$alias.'.id'
			.' INNER JOIN {{material_receipt}} receipt ON receiptItem.materialReceiptId=receipt.id';
		$criteria->compare('receipt.projectId', $projectId);
		$criteria->compare('materialId', $materialId);
		$criteria->addNotInCondition('receiptItem.id', array(null));
		$criteria->group='receiptItem.materialModelId';
		$criteria->order=$alias.'.model ASC';
		$criteria->select = $alias.'.id, '.$alias.'.model, '.$alias.'.unit,'
			.' sum(amountStandardUnit) as amount, sum(totalPrice) as grandCost,'
			.' max(standardUnitPrice) as maxUnitPrice, avg(standardUnitPrice) as averageUnitPrice, min(standardUnitPrice) as minUnitPrice';
		$command = $commandBuilder->createFindCommand($materialModelModel->getTableSchema(), $criteria);
		$rows = $command->query();
		$data=array();
		if($rows->valid()){
			foreach($rows as $row) {
				$id=$row['id'];
				$data[]=array(
					'id'=>'o_'.$id,
					'name'=>$row['model'],
					'unit'=>$row['unit'],
					'iconCls' =>'icon-cog',
					'leaf' => true,
					'totalRequisition'=>isset($totalRequisitions[$id])?round($totalWarehouse[$id],2):0,
					'totalWarehouse'=>isset($totalWarehouse[$id])?round($totalWarehouse[$id],2):0,
					'totalReceipted'=>round($row['amount'],2),
					'grandCost'=>round($row['grandCost'],2),
					'averagePrice'=>round($row['grandCost']/$row['amount'],2),
					'maxUnitPrice'=>round($row['maxUnitPrice'],2),
					'averageUnitPrice'=>round($row['averageUnitPrice'],2),
					'minUnitPrice'=>round($row['minUnitPrice'],2),
				);
			}
		}
		echo CJSON::encode($data);
	}
	
	protected function lookUpRequisitions($materialId, $projectId){
		$commandBuilder = Yii::app()->db->getSchema()->getCommandBuilder();
		$itemModel=MaterialRequisitionItem::model();
		$alias=$itemModel->getTableAlias();
		
		$criteria=new CDbCriteria();
		$criteria->join ='INNER JOIN {{material_requisition}} requisition ON requisition.id='.$alias.'.materialRequisitionId'
			.' INNER JOIN {{material_model}} materialModel ON materialModel.id='.$alias.'.materialModelId';
		$criteria->compare('requisition.projectId', $projectId);
		$criteria->compare('materialId', $materialId);
		$criteria->group='materialModelId';
		$criteria->select = 'materialModelId, sum(amountStandardUnit) as amount';
		$command = $commandBuilder->createFindCommand($itemModel->getTableSchema(), $criteria);
		$rows = $command->query();
		$data=array();
		if($rows->valid()){
			foreach($rows as $row) {
				$data[$row['materialModelId']]=$row['amount'];
			}
		}
		return $data;
	}
	
	protected function lookUpWarehouse($materialId, $projectId){
		$commandBuilder = Yii::app()->db->getSchema()->getCommandBuilder();
		$itemModel=MaterialWarehouseInboundItem::model();
		$alias=$itemModel->getTableAlias();
		
		$criteria=new CDbCriteria();
		$criteria->join ='INNER JOIN {{material_warehouse_inbound}} inbound ON inbound.id='.$alias.'.materialWarehouseInboundId'
			.' INNER JOIN {{material_model}} materialModel ON materialModel.id='.$alias.'.materialModelId';
		$criteria->compare('inbound.projectId', $projectId);
		$criteria->compare('materialId', $materialId);
		$criteria->group='materialModelId';
		$crieria->select = 'materialModelId, sum(amountStandardUnit) as amount';
		$command = $commandBuilder->createFindCommand($itemModel->getTableSchema(), $criteria);
		$rows = $command->query();
		$data=array();
		if($rows->valid()){
			foreach($rows as $row) {
				$data[$row['materialModelId']]=$row['amount'];
			}
		}
		return $data;
	}
}